﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Drawing;
using System.Data.SqlClient;

namespace AdoHelper
{
    [Serializable]
    public class DataBase:IDataBase
    {
        #region fields and Properties
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        private string f_connectionString = "";
        public string ConnectionString
        {
            get { return f_connectionString; }
            set { f_connectionString = value; }
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        private DbProviderFactory f_Factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
        public DbProviderFactory Factory
        {
            get { return f_Factory; }
            set { f_Factory = value; }
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public event DataBaseErrorDelegate ONError;
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        private string f_SystemDateFunction = "getDate()";
        public string SystemDateFunction
        {
            get
            {
                return this.f_SystemDateFunction;
            }
            set
            {
                this.f_SystemDateFunction = value;
            }
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        private string f_IdentifierPrefix = "[";
        public string IdentifierPrefix
        {
            get { return f_IdentifierPrefix; }
            set { f_IdentifierPrefix = value; }
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        private string f_IdentifierPostfix = "]";
        public string IdentifierPostfix
        {
            get { return f_IdentifierPostfix; }
            set { f_IdentifierPostfix = value; }
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        private string f_ParameterPrefix = "@";
        public string ParameterPrefix
        {
            get
            {
                return this.f_ParameterPrefix;
            }
            set
            {
                this.f_ParameterPrefix = value;
            }
        }

        #endregion


        #region constructors
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public DataBase()
        {
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public DataBase(string connectionString)
        {
            this.f_connectionString = connectionString;
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public DataBase(string connectionString,string providerName)
        {
            this.f_connectionString = connectionString;
            f_Factory = DbProviderFactories.GetFactory(providerName);
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        #endregion


        #region private Methods
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        private void CorrectNullAndSizeError(params DbParameter[] p)
        {
            for (int i = 0; i < p.Length; i++)
            {
                if (p[i].Value == null)
                {
                    p[i].Value = DBNull.Value;
                }
                if (p[i].Size == 0)
                {
                    p[i].Size = 999999999;
                }
            }
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        #endregion


        #region public Methods
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public virtual object ExecuteScalar(string query, CommandType c, ref Exception error, params DbParameter[] p)
        {
            this.CorrectNullAndSizeError(p);
            error = null;
            object scalar = null;
            using (DbConnection connection =this.f_Factory.CreateConnection())
            {
                connection.ConnectionString = this.ConnectionString;
                using (DbCommand command =this.f_Factory.CreateCommand())
                {
                    try
                    {
                        command.CommandText=query;
                        command.Connection = connection;
                        command.CommandType = c;
                        command.Parameters.AddRange(p);
                        connection.Open();
                        scalar = command.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        if (this.ONError != null)
                        {
                            this.ONError(this, new DataBaseErrorEventArgs(ex));
                        }
                        error = ex;
                    }
                    command.Dispose();
                }
                connection.Close();
                connection.Dispose();
            }
            return scalar;
        }
        public virtual object ExecuteScalar(string query, CommandType c, DbConnection connection, DbTransaction trnsaction, DbCommand command, ref Exception error, params DbParameter[] p)
        {
            this.CorrectNullAndSizeError(p);
            error = null;
            bool disposeConnection = false;
            bool disposeCommand = false;
            object scalar = null;
            try
            {
                if (command == null)
                {
                    command = this.f_Factory.CreateCommand();
                    disposeCommand = true;
                }
                if (command.Connection == null)
                {
                    if (trnsaction != null && trnsaction.Connection != null)
                    {
                        command.Connection = trnsaction.Connection;
                    }
                    else if (connection != null)
                    {
                        command.Connection = connection;
                    }
                    else
                    {
                        command.Connection = this.f_Factory.CreateConnection();
                        disposeConnection = true;
                    }
                }
                command.Transaction = trnsaction;
                command.CommandText = query;
                command.CommandType = c;
                    command.Parameters.AddRange(p);
                    if (command.Connection.State == ConnectionState.Closed)
                {
                    command.Connection.ConnectionString = this.f_connectionString;
                    command.Connection.Open();
                }
                scalar = command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                if (this.ONError != null)
                {
                    this.ONError(this, new DataBaseErrorEventArgs(ex));
                }
                error = ex;
            }
            finally
            {
                if (disposeConnection)
                {
                    command.Connection.Close();
                    command.Connection.Dispose();
                    command.Connection = null;
                }
                if (disposeCommand)
                {
                    command.Dispose();
                    command = null;
                }
            }
            return scalar;
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public virtual DbDataReader ExcuteReader(string query, CommandType c, ref Exception error, params DbParameter[] p)
        {
            this.CorrectNullAndSizeError(p);
            error = null;
            DbDataReader reader = null;
            DbConnection connection = this.f_Factory.CreateConnection();
            connection.ConnectionString = this.ConnectionString;
            using (DbCommand command = this.f_Factory.CreateCommand())
            {
                try
                {
                    command.CommandText = query;
                    command.Connection = connection;
                    command.CommandType = c;
                    command.Parameters.AddRange(p);
                    connection.Open();
                    reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                }
                catch (Exception ex)
                {
                    if (this.ONError != null)
                    {
                        this.ONError(this, new DataBaseErrorEventArgs(ex));
                    }
                    error = ex;
                }
                command.Dispose();
            }
            return reader;
        }
        public virtual DbDataReader ExcuteReader(string query, CommandType c, DbConnection connection, DbTransaction trnsaction, DbCommand command, ref Exception error, params DbParameter[] p)
        {
            this.CorrectNullAndSizeError(p);
            error = null;
            bool disposeCommand = false;
            DbDataReader reader = null;
            try
            {
                if (command == null)
                {
                    command =  this.f_Factory.CreateCommand();
                    disposeCommand = true;
                }
                if (command.Connection == null)
                {
                    if (trnsaction != null && trnsaction.Connection != null)
                    {
                        command.Connection = trnsaction.Connection;
                    }
                    else if (connection != null)
                    {
                        command.Connection = connection;
                    }
                    else
                    {
                        command.Connection = this.f_Factory.CreateConnection();
                    }
                }
                command.Transaction = trnsaction;
                command.CommandText = query;
                command.CommandType = c;
                command.Parameters.AddRange(p);
                if (command.Connection.State == ConnectionState.Closed)
                {
                    command.Connection.ConnectionString = this.f_connectionString;
                    command.Connection.Open();
                }
                reader = command.ExecuteReader();
            }
            catch (Exception ex)
            {
                if (this.ONError != null)
                {
                    this.ONError(this, new DataBaseErrorEventArgs(ex));
                }
                error = ex;
            }
            finally
            {
                if (disposeCommand)
                {
                    command.Dispose();
                    command = null;
                }
            }
            return reader;
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public virtual int ExecuteNonQuery(string query, CommandType c, ref Exception error, params DbParameter[] p)
        {
            this.CorrectNullAndSizeError(p);
            error = null;
            int rowsAffected = -1;
            using (DbConnection connection = this.f_Factory.CreateConnection())
            {
                connection.ConnectionString = this.ConnectionString;
                using (DbCommand command = this.f_Factory.CreateCommand())
                {
                    try
                    {
                        command.CommandText = query;
                        command.Connection = connection;
                        command.CommandType = c;
                        command.Parameters.AddRange(p);
                        command.CommandTimeout = 0;
                        connection.Open();
                        rowsAffected = command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        if (this.ONError != null)
                        {
                            this.ONError(this, new DataBaseErrorEventArgs(ex));
                        }
                        error = ex;
                    }
                    command.Dispose();
                }
                connection.Close();
                connection.Dispose();
            }
            return rowsAffected;
        }
        public virtual int ExecuteNonQuery(string query, CommandType c, DbConnection connection, DbTransaction trnsaction, DbCommand command, ref Exception error, params DbParameter[] p)
        {
            this.CorrectNullAndSizeError(p);
            error = null;
            bool disposeConnection = false;
            bool disposeCommand = false;
            int rowsAffected = -1;
            try
            {
                if (command == null)
                {
                    command =  this.f_Factory.CreateCommand();
                    disposeCommand = true;
                }
                if (command.Connection == null)
                {
                    if (trnsaction != null && trnsaction.Connection != null)
                    {
                        command.Connection = trnsaction.Connection;
                    }
                    else if (connection != null)
                    {
                        command.Connection = connection;
                    }
                    else
                    {
                        command.Connection = this.f_Factory.CreateConnection();
                        disposeConnection = true;
                    }
                }
                command.Transaction = trnsaction;
                command.CommandText = query;
                command.CommandType = c;
                command.Parameters.AddRange(p);
                if (command.Connection.State == ConnectionState.Closed)
                {
                    command.Connection.ConnectionString = this.f_connectionString;
                    command.Connection.Open();
                }
                rowsAffected = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                if (this.ONError != null)
                {
                    this.ONError(this, new DataBaseErrorEventArgs(ex));
                }
                error = ex;
            }
            finally
            {
                if (disposeConnection)
                {
                    command.Connection.Close();
                    command.Connection.Dispose();
                    command.Connection = null;
                }
                if (disposeCommand)
                {
                    command.Dispose();
                    command = null;
                }
            }
            return rowsAffected;
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public virtual DataSet GetDataSet(string query, CommandType c, ref Exception error, params DbParameter[] p)
        {
            this.CorrectNullAndSizeError(p);
            error = null;
            DataSet ds = null;
            using (DbConnection connection = this.f_Factory.CreateConnection())
            {
                connection.ConnectionString = this.ConnectionString;
                using (DbCommand command = this.f_Factory.CreateCommand())
                {
                    try
                    {
                        command.CommandText = query;
                        command.Connection = connection;
                        command.CommandType = c;
                        command.Parameters.AddRange(p);
                        command.CommandTimeout = 0;
                        connection.Open();
                        using (DbDataAdapter adapter = this.f_Factory.CreateDataAdapter())
                        {
                            adapter.SelectCommand = command;
                            ds = new DataSet();
                            adapter.Fill(ds);
                            adapter.Dispose();
                        }
                    }
                    catch (Exception ex)
                    {
                        if (this.ONError != null)
                        {
                            this.ONError(this, new DataBaseErrorEventArgs(ex));
                        }
                        error = ex;
                    }
                    command.Dispose();
                }
                connection.Close();
                connection.Dispose();
            }
            return ds;
        }
        public virtual DataSet GetDataSet(string query, CommandType c, DbConnection connection, DbCommand command, DbDataAdapter adapter, ref Exception error, params DbParameter[] p)
        {
            this.CorrectNullAndSizeError(p);
            error = null;
            bool disposeAdapter = false;
            bool disposeConnection = false;
            bool disposeCommand = false;
            DataSet ds = new DataSet ();
            try
            {
                if (adapter == null)
                {
                    adapter = this.f_Factory.CreateDataAdapter();
                    disposeAdapter = true;
                }
                if (adapter.SelectCommand == null)
                {
                    adapter.SelectCommand =  this.f_Factory.CreateCommand();
                    disposeCommand = true;
                }
                if (adapter.SelectCommand.Connection == null)
                {
                    if (connection != null)
                    {
                        adapter.SelectCommand.Connection = connection;
                    }
                    else
                    {
                        adapter.SelectCommand.Connection = this.f_Factory.CreateConnection();
                        disposeConnection = true;
                    }
                }
                adapter.SelectCommand.CommandText = query;
                adapter.SelectCommand.CommandType = c;
                adapter.SelectCommand.Parameters.AddRange(p);
                if (adapter.SelectCommand.Connection.State == ConnectionState.Closed)
                {
                    adapter.SelectCommand.Connection.ConnectionString = this.f_connectionString;
                    adapter.SelectCommand.Connection.Open();
                }
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                if (this.ONError != null)
                {
                    this.ONError(this, new DataBaseErrorEventArgs(ex));
                }
                error = ex;
            }
            finally
            {
                if (disposeConnection)
                {
                    adapter.SelectCommand.Connection.Close();
                    adapter.SelectCommand.Connection.Dispose();
                    adapter.SelectCommand.Connection = null;
                }
                if (disposeCommand)
                {
                    adapter.SelectCommand.Dispose();
                    adapter.SelectCommand = null;
                }
                if (disposeAdapter)
                {
                    adapter.Dispose();
                    adapter = null;
                }
            }
            return ds;
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public virtual DataTable GetDataTable(string query, CommandType c, ref Exception error, params DbParameter[] p)
        {
            this.CorrectNullAndSizeError(p);
            error = null;
            DataTable dt = null;
            using (DbConnection connection = this.f_Factory.CreateConnection())
            {
                connection.ConnectionString = this.ConnectionString;
                using (DbCommand command = this.f_Factory.CreateCommand())
                {
                    try
                    {
                        command.CommandText = query;
                        command.Connection = connection;
                        command.CommandType = c;
                        command.Parameters.AddRange(p);
                        command.CommandTimeout = 0;
                        connection.Open();
                        using (DbDataAdapter adapter = this.f_Factory.CreateDataAdapter())
                        {
                            adapter.SelectCommand = command;
                            dt = new DataTable();
                            adapter.Fill(dt);
                            adapter.Dispose();
                        }
                    }
                    catch (Exception ex)
                    {
                        if (this.ONError != null)
                        {
                            this.ONError(this, new DataBaseErrorEventArgs(ex));
                        }
                        error = ex;
                    }
                    command.Dispose();
                }
                connection.Close();
                connection.Dispose();
            }
            return dt;
        }
        public virtual DataTable GetDataTable(string query, CommandType c, DbConnection connection, DbCommand command, DbDataAdapter adapter, ref Exception error, params DbParameter[] p)
        {
            this.CorrectNullAndSizeError(p);
            error = null;
            bool disposeAdapter = false;
            bool disposeConnection = false;
            bool disposeCommand = false;
            DataTable dt = new DataTable ();
            try
            {
                if (adapter == null)
                {
                    adapter = this.f_Factory.CreateDataAdapter();
                    disposeAdapter = true;
                }
                if (adapter.SelectCommand == null)
                {
                    adapter.SelectCommand = this.f_Factory.CreateCommand();
                    disposeCommand = true;
                }
                if (adapter.SelectCommand.Connection == null)
                {
                    if (connection != null)
                    {
                        adapter.SelectCommand.Connection = connection;
                    }
                    else
                    {
                        adapter.SelectCommand.Connection = this.f_Factory.CreateConnection();
                        disposeConnection = true;
                    }
                }
                adapter.SelectCommand.CommandText = query;
                adapter.SelectCommand.CommandType = c;
                adapter.SelectCommand.Parameters.AddRange(p);
                if (adapter.SelectCommand.Connection.State == ConnectionState.Closed)
                {
                    adapter.SelectCommand.Connection.ConnectionString = this.f_connectionString;
                    adapter.SelectCommand.Connection.Open();
                }
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                if (this.ONError != null)
                {
                    this.ONError(this, new DataBaseErrorEventArgs(ex));
                }
                error = ex;
            }
            finally
            {
                if (disposeConnection)
                {
                    adapter.SelectCommand.Connection.Close();
                    adapter.SelectCommand.Connection.Dispose();
                    adapter.SelectCommand.Connection = null;
                }
                if (disposeCommand)
                {
                    adapter.SelectCommand.Dispose();
                    adapter.SelectCommand = null;
                }
                if (disposeAdapter)
                {
                    adapter.Dispose();
                    adapter = null;
                }
            }
            return dt;
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public virtual DateTime GetServerDate(ref Exception error)
        {
            error = null;
            return (DateTime)this.ExecuteScalar("SELECT GETDATE()", CommandType.Text, ref error);
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public virtual DbDataAdapter GetAdapter(string query, CommandType c, params DbParameter[] p)
        {
            this.CorrectNullAndSizeError(p);
            DbConnection connection = this.f_Factory.CreateConnection();
            connection.ConnectionString = this.f_connectionString;
            DbCommand command = this.f_Factory.CreateCommand();
            command.CommandText = query;
            command.Connection = connection;
            command.CommandType = c;
            command.Parameters.AddRange(p);
            command.CommandTimeout = 0;
            DbDataAdapter adapter = this.f_Factory.CreateDataAdapter();
            adapter.SelectCommand = command;
            return adapter;
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public virtual Exception TestConnection()
        {
            Exception er = null;
            try
            {
                this.GetDataTable("SELECT 'test'", CommandType.Text, ref er);
            }
            catch(Exception e)
            {
                er = e;
            }
            return er;
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        #endregion


        #region static methods
        public static DataTable GetTotalsRowForDataTable(DataTable dt, string RowHeader, ref Exception error, params string[] columnsNames)
        {
            error = null;
            try
            {
                DataTable newDt = dt.Copy();
                DataRow totalRow = newDt.NewRow();
                newDt.Rows.Add(totalRow);
                if (columnsNames.Length == 0)
                {
                    columnsNames = new string[dt.Columns.Count];
                    for (int i = 0; i < columnsNames.Length; i++)
                    {
                        columnsNames[i] = dt.Columns[i].ColumnName;
                    }
                }

                for (int i = 0; i < columnsNames.Length; i++)
                {
                    double sum = 0;
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        double rowValue;
                        double.TryParse(dt.Rows[j][columnsNames[i]].ToString(), out rowValue);
                        sum += rowValue;
                    }
                    totalRow[columnsNames[i]] = sum;
                }
                if (RowHeader != "")
                {
                    totalRow[0] = RowHeader;
                }
                return newDt;
            }
            catch (Exception er)
            {
                error = er;
                return null;
            }
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public static DataTable GetTotalsColumnForDataTable(DataTable dt, string columnHeader, ref Exception error, params string[] columnsNames)
        {
            error = null;
            try
            {
                DataTable newDt = dt.Copy();
                newDt.Columns.Add(columnHeader);

                if (columnsNames.Length == 0)
                {
                    columnsNames = new string[dt.Columns.Count];
                    for (int i = 0; i < columnsNames.Length; i++)
                    {
                        columnsNames[i] = dt.Columns[i].ColumnName;
                    }
                }

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    double sum = 0;
                    for (int j = 0; j < columnsNames.Length; j++)
                    {
                        double columnValue;
                        double.TryParse(dt.Rows[i][columnsNames[j]].ToString(), out columnValue);
                        sum += columnValue;
                    }
                    //assign the totals to the table
                    newDt.Rows[i][columnHeader] = sum;
                }
                return newDt;

            }
            catch (Exception er)
            {
                error = er;
                return null;
            }
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public static string[] ConvertDataTableToHtml(Color columnsHeadersBackColor, Color columnsHeadersForeColor, Color RowsHeadersBackColor, Color RowsHeadersForeColor, Color tableBackColor, Color numbersColor, Color stringsColor, DataTable dt, ref Exception error)
        {
            error = null;
            try
            {
                string[] htmlStringsOfDataTable = new string[dt.Rows.Count + 1];
                for (int i = -1; i < dt.Rows.Count; i++)
                {
                    string htmlOfRow = "<tr align=center>";
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        string value;
                        string backGroundColor;
                        string fontColor;
                        if (i == -1)
                        {
                            value = "<b>" + dt.Columns[j].Caption + "</b>";
                            backGroundColor = ColorTranslator.ToHtml(columnsHeadersBackColor);
                            fontColor = ColorTranslator.ToHtml(columnsHeadersForeColor);
                        }
                        else
                        {
                            if (j == 0)
                            {
                                value = "<b>" + dt.Rows[i][0].ToString() + "</b>";
                                backGroundColor = ColorTranslator.ToHtml(RowsHeadersBackColor);
                                fontColor = ColorTranslator.ToHtml(RowsHeadersForeColor);
                            }
                            else
                            {
                                value = dt.Rows[i][j].ToString();
                                backGroundColor = ColorTranslator.ToHtml(tableBackColor);
                                double temp;
                                if (double.TryParse(value, out temp))
                                {
                                    fontColor = ColorTranslator.ToHtml(numbersColor);
                                }
                                else
                                {
                                    fontColor = ColorTranslator.ToHtml(stringsColor);
                                }
                            }
                        }
                        htmlOfRow += string.Format("<td bgcolor={0}><font color={1}>{2}</font></td>", backGroundColor, fontColor, value);
                    }
                    htmlOfRow += "</tr>";

                    if (i == -1)
                    {
                        htmlOfRow = "<table border=1>" + htmlOfRow;
                    }
                    else if (i == dt.Rows.Count - 1)
                    {
                        htmlOfRow = htmlOfRow + "</table>";
                    }
                    htmlStringsOfDataTable[i + 1] = htmlOfRow;

                }
                return htmlStringsOfDataTable;
            }
            catch (Exception er)
            {
                error = er;
                return null;
            }
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public static DataTable GetSubDataTable(DataTable dt, string rowsFilterCondition, DataViewRowState rowsStates, bool distinct, string OrderCondition, ref Exception error, params string[] columnsNames)
        {
            error = null;
            try
            {
                DataView dv = new DataView(dt);
                dv.RowFilter = rowsFilterCondition;
                dv.RowStateFilter = rowsStates;
                dv.Sort = OrderCondition;
                return dv.ToTable(distinct, columnsNames);
            }
            catch (Exception er)
            {
                error = er;
                return null;
            }
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public static string concatenateColumnToString(DataView dv, string columnName, string delimiters, ref Exception error)
        {
            error = null;
            try
            {
                string statement = "";
                for (int i = 0; i < dv.Count; i++)
                {
                    string value = dv[i][columnName].ToString();
                    if (statement != "")
                    {
                        statement += delimiters;
                    }
                    statement += value;
                }
                return statement;
            }
            catch (Exception er)
            {
                error = er;
                return "";
            }

        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public static DataTable FlipDataTable(DataTable dt, string verticalColumn, string horizontalColumn, string countColumn, ref Exception error)
        {
            error = null;
            try
            {
                DataTable flippedDataTable = new DataTable();
                flippedDataTable.Columns.Add("RowsHeaders");
                List<string> rowsHeaders = new List<string>();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string columnName = dt.Rows[i][horizontalColumn].ToString();
                    if (columnName == "")
                    {
                        columnName = "column1";
                    }
                    if (!flippedDataTable.Columns.Contains(columnName))
                    {
                        flippedDataTable.Columns.Add(columnName);
                    }
                    string rowHeaderName = dt.Rows[i][verticalColumn].ToString();
                    if (!rowsHeaders.Contains(rowHeaderName))
                    {
                        rowsHeaders.Add(rowHeaderName);
                        flippedDataTable.Rows.Add(flippedDataTable.NewRow());
                        flippedDataTable.Rows[flippedDataTable.Rows.Count - 1]["RowsHeaders"] = rowHeaderName;
                        flippedDataTable.Rows[flippedDataTable.Rows.Count - 1][columnName] = dt.Rows[i][countColumn];
                    }
                    else
                    {
                        DataRow dr = flippedDataTable.Select("RowsHeaders = '" + rowHeaderName + "'")[0];
                        double existValue = 0.0;
                        double.TryParse((dr[columnName].ToString()), out existValue);
                        dr[columnName] = existValue + double.Parse(dt.Rows[i][countColumn].ToString());
                    }
                }
                return flippedDataTable;
            }
            catch (Exception er)
            {
                error = er;
                return null;
            }

        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public static DataTable PartOfDataTable(DataTable dt, int rowFrom, int rowTo)
        {
            DataTable partDataTable = null;
            if (dt != null)
            {
                DataView dv = new DataView(dt);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    partDataTable.Columns.Add(dt.Columns[i].ColumnName);
                }
                for (int i = rowFrom; i <= rowTo; i++)
                {
                }
            }
            return partDataTable;
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public static DataTable Intersect(DataTable primaryTable, DataTable secondaryTable, string[] primaryTableJoinColumns, string[] secondaryJoinTableColumns)
        {
            DataTable dt = primaryTable.Clone();
            for (int i = 0; i < primaryTable.Rows.Count; i++)
            {
                string Filter = "";
                for (int j = 0; j < secondaryJoinTableColumns.Length; j++)
                {
                    if (Filter != "")
                    {
                        Filter += " AND ";
                    }
                    if (primaryTable.Rows[i][primaryTableJoinColumns[j]] == DBNull.Value)
                    {
                        Filter += string.Format(secondaryJoinTableColumns[j] + " IS NULL");
                    }
                    else
                    {
                        Filter += string.Format(secondaryJoinTableColumns[j] + "='{0}'", primaryTable.Rows[i][primaryTableJoinColumns[j]]);
                    }
                }
                if (secondaryTable.Select(Filter).Length > 0)
                {
                    dt.Rows.Add(primaryTable.Rows[i].ItemArray);
                }
            }
            return dt;
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public static DataTable Difference(DataTable primaryTable, DataTable secondaryTable, string[] primaryTableJoinColumns, string[] secondaryTableJoinColumns)
        {
            DataTable dt = primaryTable.Clone();
            for (int i = 0; i < primaryTable.Rows.Count; i++)
            {
                string Filter = "";
                for (int j = 0; j < secondaryTableJoinColumns.Length; j++)
                {
                    if (Filter != "")
                    {
                        Filter += " AND ";
                    }
                    if (primaryTable.Rows[i][primaryTableJoinColumns[j]] == DBNull.Value)
                    {
                        Filter += string.Format(secondaryTableJoinColumns[j] + " IS NULL");
                    }
                    else
                    {
                        Filter += string.Format(secondaryTableJoinColumns[j] + "='{0}'", primaryTable.Rows[i][primaryTableJoinColumns[j]]);
                    }
                }
                if (secondaryTable.Select(Filter).Length == 0)
                {
                    dt.Rows.Add(primaryTable.Rows[i].ItemArray);
                }
            }
            return dt;
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public static DataTable AddColumns(DataTable primaryTable, DataTable secondaryTable, string[] primaryTableJoinColumns, string[] secondaryTableJoinColumns, string[] primaryTableDisplayColumns, string[] secondaryTableSelectColumns)
        {
            DataTable dt = primaryTable.Copy();
            for (int i = 0; i < primaryTableDisplayColumns.Length; i++)
            {
                 dt.Columns.Add(primaryTableDisplayColumns[i], secondaryTable.Columns[secondaryTableSelectColumns[i]].DataType);
            }
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string Filter = "";
                for (int j = 0; j < secondaryTableJoinColumns.Length; j++)
                {
                    if (Filter != "")
                    {
                        Filter += " AND ";
                    }
                    if (primaryTable.Rows[i][primaryTableJoinColumns[j]] == DBNull.Value)
                    {
                        Filter += string.Format(secondaryTableJoinColumns[j] + " IS NULL");
                    }
                    else
                    {
                        Filter += string.Format(secondaryTableJoinColumns[j] + "='{0}'", dt.Rows[i][primaryTableJoinColumns[j]]);
                    }
                }
                DataRow [] filteredRows=secondaryTable.Select(Filter);
                if (filteredRows.Length > 0)
                {
                    for (int j = 0; j < primaryTableDisplayColumns.Length; j++)
                    {
                        dt.Rows[i][primaryTableDisplayColumns[j]] = filteredRows[0][secondaryTableSelectColumns[j]];
                    }
                }
            }
            return dt;
        }        
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        public static string EscapeSqlCharacter(string Statement,bool LikeCharacters)
        {
            string s = Statement;
            if (LikeCharacters)
            {
                s = s.Replace("[", "[[]");
                s = s.Replace("%", "[%]");
                s = s.Replace("_", "[_]");
            }
            s = s.Replace("'", "''");
            return s;
        }
        //=================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================--
        #endregion


    }
}
